# coding:utf8
import traceback

import pymysql
from sqlalchemy import create_engine
from DBUtils.PooledDB import PooledDB
# sqlalchemy连接池
from request_cjbx.settings import config

ENGINE = create_engine('mysql+mysqlconnector://insurance:insurance@120.55.189.14:3306/insurance', pool_size=10, echo=True, echo_pool=True,
                       pool_recycle=3600)


# pymysql连接池
class PymysqlPool(object):
    __pool = None

    # PT数据库创建con和cursor
    def __enter__(self):
        # print '__enter__'
        self.conn = self.__getConn()
        self.cursor = self.conn.cursor()
        return self

    def __getConn(self):
        if self.__pool is None:
            self.__pool = PooledDB(
                creator=pymysql,

                # host='118.178.232.65',
                # user='insurance',
                # password='insurance',
                # db='insurance',

                host=config.host,
                user=config.user,
                password=config.password,
                db=config.db,

                charset='utf8mb4',
                cursorclass=pymysql.cursors.DictCursor
            )

        return self.__pool.connection()

    # PT连接池释放con和cursor
    def __exit__(self, type, value, trace):
        # print '__exit__'
        self.cursor.close()
        self.conn.close()

    def execute_sql(self, sql, args):
        try:
            with self as db:
                db.cursor.execute(sql, args)
                db.conn.commit()
        except Exception as e:
            print e
            print traceback.format_exc()
            return False

        return True

    def fetch_sql(self, sql):
        data = None
        try:
            with self as db:
                if sql:
                    db.cursor.execute(sql)
                data = db.cursor.fetchall()
        except Exception as e:
            print e
            print traceback.format_exc()
        return data


class MysqlModel(PymysqlPool):
    def execute(self, sql, args=None):
        return self.execute_sql(sql, args)

    def fetch(self, sql=None):
        return self.fetch_sql(sql)


# 测试
if __name__ == "__main__":
    sql = "INSERT INTO TEST(a) VALUES(2);"

    dict_1 = {"a": '1', "b": '2'}

    mysql = MysqlModel()

    sql = "INSERT INTO TEST(a,b) VALUES({0},{1});".format(dict_1['a'], dict_1['b'])
    # sql = "INSERT INTO `TEST` (`a`, `b`) VALUES('1', '2');"
    print sql
    mysql.execute(sql)
